
package org.dyndns.cooland.updater.db.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.dyndns.cooland.updater.db.MyBatisConnectionFactory;
import org.dyndns.cooland.updater.db.model.Category;

public class CategoryDAO {
	
	private SqlSessionFactory	sqlSessionFactory;
	
	public CategoryDAO () {

		sqlSessionFactory = MyBatisConnectionFactory.getSqlSessionFactory();
	}
	
	public List<Category> selectAll() {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			List<Category> list = session.selectList("Category.selectAll");
			return list;
		}
		finally{
			session.close();
		}
	}
	
	public Category selectById(int id) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			Category bean = (Category) session.selectOne("Category.selectById",
					id);
			return bean;
		}
		finally{
			session.close();
		}
	}
	
	public Category selectByName(String category) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			Category bean = (Category) session.selectOne(
					"Category.selectByName", category);
			return bean;
		}
		finally{
			session.close();
		}
	}
	
	public void update(Category cat) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			session.update("Category.update", cat);
			session.commit();
		}
		finally{
			session.close();
		}
	}
	
	public void insert(Category cat) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			session.insert("Category.insert", cat);
			session.commit();
		}
		finally{
			session.close();
		}
	}
	
	public void delete(int id) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			session.delete("Category.deleteById", id);
			session.commit();
		}
		finally{
			session.close();
		}
	}
	
	public List<Category> selectByParentId(int id) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			List<Category> list = session.selectList(
					"Category.selectByParentId", id);
			return list;
		}
		finally{
			session.close();
		}
	}
	/*
	 * private Connection getConnection() throws SQLException {
	 * 
	 * Connection conn = ConnectionFactory.getInstance().getConnection(); return
	 * conn; }
	 * 
	 * // TODO make a name unique by DAO public int add(Category bean) {
	 * 
	 * int newId = -1; try{ String sql =
	 * "insert into category(name,parent_id) values(?,?)"; connection =
	 * getConnection(); ptmt = connection.prepareStatement(sql,
	 * PreparedStatement.RETURN_GENERATED_KEYS); ptmt.setString(1,
	 * bean.getName()); ptmt.setInt(2, bean.getParent_id());
	 * ptmt.executeUpdate(); // get autogenerated key ResultSet keyResultSet =
	 * ptmt.getGeneratedKeys(); if (keyResultSet.next()){ newId = (int)
	 * keyResultSet.getInt(1); } } catch (SQLException e){ e.printStackTrace();
	 * } finally{ try{ if (ptmt != null){ ptmt.close(); } if (connection !=
	 * null){ connection.close(); } } catch (SQLException e){
	 * e.printStackTrace(); } catch (Exception e){ e.printStackTrace(); } }
	 * return newId; }
	 * 
	 * public void update(Category bean) {
	 * 
	 * try{ String sql = "update category set name=?,parent_id=? where id=?";
	 * connection = getConnection(); ptmt = connection.prepareStatement(sql);
	 * ptmt.setString(1, bean.getName()); ptmt.setInt(2, bean.getParent_id());
	 * ptmt.setInt(3, bean.getId()); ptmt.executeUpdate(); } catch (SQLException
	 * e){ e.getSQLState(); e.printStackTrace(); } finally{ try{ if (ptmt !=
	 * null){ ptmt.close(); } if (connection != null){ connection.close(); } }
	 * catch (SQLException e){ e.printStackTrace(); } catch (Exception e){
	 * e.printStackTrace(); } } }
	 * 
	 * public void delete(Category bean) {
	 * 
	 * try{ String sql = "delete from category where id=?"; connection =
	 * getConnection(); ptmt = connection.prepareStatement(sql); ptmt.setInt(1,
	 * bean.getId()); ptmt.executeUpdate(); } catch (SQLException e){
	 * e.printStackTrace(); } finally{ try{ if (ptmt != null){ ptmt.close(); }
	 * if (connection != null){ connection.close(); } } catch (SQLException e){
	 * e.printStackTrace(); } catch (Exception e){ e.printStackTrace(); } } }
	 * 
	 * public Category getById(int id) {
	 * 
	 * Category bean = null; try{ String sql =
	 * "select * from category where id=?"; connection = getConnection(); ptmt =
	 * connection.prepareStatement(sql); ptmt.setInt(1, id); resultSet =
	 * ptmt.executeQuery(); while (resultSet.next()){ bean = new Category();
	 * bean.setId(resultSet.getInt("id"));
	 * bean.setName(resultSet.getString("name"));
	 * bean.setParent_id(resultSet.getInt("parent_id")); } } catch (SQLException
	 * e){ e.printStackTrace(); } finally{ try{ if (ptmt != null){ ptmt.close();
	 * } if (connection != null){ connection.close(); } } catch (SQLException
	 * e){ e.printStackTrace(); } catch (Exception e){ e.printStackTrace(); } }
	 * return bean; }
	 * 
	 * public List<Category> getByParentId(int parent_id) {
	 * 
	 * List<Category> list = new ArrayList<Category>(); try{ String sql =
	 * "select * from category where parent_id=?"; connection = getConnection();
	 * ptmt = connection.prepareStatement(sql); ptmt.setInt(1, parent_id);
	 * resultSet = ptmt.executeQuery(); while (resultSet.next()){ Category bean
	 * = new Category(); bean.setId(resultSet.getInt("id"));
	 * bean.setName(resultSet.getString("name"));
	 * bean.setParent_id(resultSet.getInt("parent_id")); list.add(bean); } }
	 * catch (SQLException e){ e.printStackTrace(); } finally{ try{ if (ptmt !=
	 * null){ ptmt.close(); } if (connection != null){ connection.close(); } }
	 * catch (SQLException e){ e.printStackTrace(); } catch (Exception e){
	 * e.printStackTrace(); } } return list; }
	 * 
	 * public Category getByName(String name) {
	 * 
	 * Category bean = null; try{ String sql =
	 * "select * from category where name=?"; connection = getConnection(); ptmt
	 * = connection.prepareStatement(sql); ptmt.setString(1, name); resultSet =
	 * ptmt.executeQuery(); while (resultSet.next()){ bean = new Category();
	 * bean.setId(resultSet.getInt("id"));
	 * bean.setName(resultSet.getString("name"));
	 * bean.setParent_id(resultSet.getInt("parent_id")); } } catch (SQLException
	 * e){ e.printStackTrace(); } finally{ try{ if (ptmt != null){ ptmt.close();
	 * } if (connection != null){ connection.close(); } } catch (SQLException
	 * e){ e.printStackTrace(); } catch (Exception e){ e.printStackTrace(); } }
	 * return bean; }
	 * 
	 * public List<Category> findAll() {
	 * 
	 * List<Category> list = new ArrayList<Category>(); try{ String sql =
	 * "select * from category"; connection = getConnection(); ptmt =
	 * connection.prepareStatement(sql); resultSet = ptmt.executeQuery(); while
	 * (resultSet.next()){ Category bean = new Category();
	 * bean.setId(resultSet.getInt("id"));
	 * bean.setName(resultSet.getString("name"));
	 * bean.setParent_id(resultSet.getInt("parent_id")); list.add(bean); } }
	 * catch (SQLException e){ e.printStackTrace(); } finally{ try{ if (ptmt !=
	 * null){ ptmt.close(); } if (connection != null){ connection.close(); } }
	 * catch (SQLException e){ e.printStackTrace(); } catch (Exception e){
	 * e.printStackTrace(); } } return list; }
	 */
}
